package org.gdstash.db;

import org.gdstash.file.ARZRecord;
import org.gdstash.ui.GDStashFrame;
import org.gdstash.util.GDConstants;
import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class GDDBData {
    public static final String DERBY_LANG_DUPLICATE_KEY_CONSTRAINT = "23505";
    private static final String oldURL = "jdbc:derby:db;";
    private static final int DERBY_TRACE_NONE = 0;
    private static final int DERBY_TRACE_CONNECTION_CALLS = 1;
    private static final int DERBY_TRACE_STATEMENT_CALLS = 2;
    private static final int DERBY_TRACE_RESULT_SET_CALLS = 4;
    private static final int DERBY_TRACE_DRIVER_CONFIGURATION = 16;
    private static final int DERBY_TRACE_CONNECTS = 32;
    private static final int DERBY_TRACE_PROTOCOL_FLOWS = 64;
    private static final int DERBY_TRACE_RESULT_SET_META_DATA = 128;
    private static final int DERBY_TRACE_PARAMETER_META_DATA = 256;
    private static final int DERBY_TRACE_DIAGNOSTICS = 512;
    private static final int DERBY_TRACE_ALL = -1;
    private static String dbURL;
    private static String mod;
    private static final String derbyLocale = "en_US";

    private static void fillDBUrl(final String mod) {
        String dbDir = null;
        if (mod == null) {
            dbDir = "db";
        } else if (mod.isEmpty()) {
            dbDir = "db";
        } else {
            dbDir = "db_" + mod;
        }
        final String dir = GDConstants.USER_HOME + GDConstants.FILE_SEPARATOR + "GDStash" + GDConstants.FILE_SEPARATOR + dbDir;
        GDDBData.dbURL = "jdbc:derby:" + dir + ";";
    }

    public static boolean doesExist() {
        fillDBUrl(null);
        return GDDBUtil.doesExist(GDDBData.dbURL);
    }

    public static boolean doesExist(final String mod) {
        fillDBUrl(mod);
        return GDDBUtil.doesExist(GDDBData.dbURL);
    }

    public static Connection getConnection() throws SQLException {
        String mod = null;
        if (GDStashFrame.iniConfig != null && GDStashFrame.iniConfig.sectHistory != null) {
            mod = GDStashFrame.iniConfig.sectHistory.lastMod;
        }
        return getConnection(mod);
    }

    private static Connection getConnection(final String mod) throws SQLException {
        fillDBUrl(mod);
        final String url = GDDBData.dbURL + "create=true;territory=" + "en_US";
        final Connection conn = DriverManager.getConnection(url);
        conn.setAutoCommit(false);
        return conn;
    }

    public static Connection getOldConnection() throws SQLException {
        final String url = "jdbc:derby:db;create=false";
        final Connection conn = DriverManager.getConnection(url);
        conn.setAutoCommit(false);
        return conn;
    }

    public static void closeConnections() {
        if (GDDBData.dbURL != null) {
            try {
                DriverManager.getConnection("jdbc:derby:;shutdown=true");
            } catch (SQLException ex) {
            }
        }
    }

    static {
        GDDBData.dbURL = null;
        GDDBData.mod = null;
    }

    public static void createConfigTables() throws SQLException {
        try (final Connection conn = getConnection()) {
            if (GDDBUtil.tableExists(conn, "INFO_CONFIG")) {
                final String version = DBConfig.getConfigVersion();
                if (version == null || !version.equals("1.0.8")) {
                    DBConfig.createTable();
                }
            } else {
                DBConfig.createTable();
            }
            if (!GDDBUtil.tableExists(conn, "STASH_ITEM_V10")) {
                DBStashItem.createTable();
            }
            conn.commit();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static boolean createDataTables() {
        boolean success = false;
        try {
            DBItem.createTables();
            DBItemSet.createTables();
            DBAffix.createTables();
            DBAffixSet.createTables();
            DBFormulaSet.createTables();
            DBLootTable.createTables();
            DBLootTableSet.createTables();
            DBSkillTree.createTables();
            DBSkill.createTables();
            DBPet.createTables();
            DBPetBio.createTables();
            DBController.createTables();
            DBShrine.createTables();
            DBConstellation.createTables();
            DBSkillButton.createTable();
            DBBitmap.createTable();
            DBInventoryGrid.createTable();
            DBCaravanWindow.createTable();
            DBClassTable.createTables();
            DBEngineGame.createTable();
            DBFaction.createTable();
            DBEngineSkillMaster.createTables();
            DBMerchant.createTables();
            DBMerchantTableSet.createTables();
            DBMerchantTable.createTables();
            DBEnginePlayer.createTable();
            DBEngineLevel.createTable();
            DBEngineTagText.createTable();
            success = true;
        } catch (Exception ex) {
            GDMsgLogger.addError(ex);
        }
        return success;
    }

    public static boolean insertData(final ARZRecord[] records, final boolean suppressTagWarning) {
        boolean success = false;
        try {
            insertRecords(records, suppressTagWarning);
            success = true;
        } catch (Exception ex) {
            GDMsgLogger.addError(ex);
        }
        return success;
    }

    public static void clearBuffers() {
        DBAffix.clearBuffer();
        DBAffixSet.clearBuffer();
        DBFormulaSet.clearBuffer();
        DBItem.clearBuffer();
        DBItemCraft.clearBuffer();
        DBLootTable.clearBuffer();
        DBLootTableSet.clearBuffer();
        DBSkill.clearBuffer();
        DBSkillTree.clearBuffer();
        DBPet.clearBuffer();
        DBPetBio.clearBuffer();
        DBShrine.clearBuffer();
        DBEngineTagText.clearBuffer();
        DBBitmap.clearBuffer();
        DBInventoryGrid.clearBuffer();
        DBCaravanWindow.clearBuffer();
        DBClassTable.clearBuffer();
        DBController.clearBuffer();
    }

    private static void insertRecords(final ARZRecord[] records, final boolean suppressTagWarning) throws SQLException {
        for (int i = 0; i < records.length; ++i) {
            if (records[i] != null) {
                try {
                    boolean processed = false;
                    if (records[i].isBitmap()) {
                        DBBitmap.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isInventoryGrid()) {
                        DBInventoryGrid.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isCaravanWindow()) {
                        DBCaravanWindow.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isClassTable()) {
                        DBClassTable.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isSkillMaster()) {
                        DBEngineSkillMaster.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isConstellation()) {
                        DBConstellation.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isSkillButton()) {
                        DBSkillButton.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isShrine()) {
                        DBShrine.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isGameEngine()) {
                        DBEngineGame.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isFaction()) {
                        DBFaction.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isMerchant()) {
                        DBMerchant.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isMerchantTableSet()) {
                        DBMerchantTableSet.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isMerchantTable()) {
                        DBMerchantTable.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isPlayerEngine()) {
                        DBEnginePlayer.insert(records[i]);
                        DBEngineLevel.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isAffix()) {
                        DBAffix.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isAffixSet()) {
                        DBAffixSet.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isItemSet()) {
                        DBItemSet.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isLootTable()) {
                        DBLootTable.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isLootTableSet()) {
                        DBLootTableSet.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isFormulaSet()) {
                        DBFormulaSet.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isSkillTree()) {
                        DBSkillTree.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isSkill()) {
                        DBSkill.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isPet()) {
                        DBPet.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isPetBio()) {
                        DBPetBio.insert(records[i]);
                        processed = true;
                    }
                    if (records[i].isController()) {
                        DBController.insert(records[i]);
                        processed = true;
                    }
                    if (!processed) {
                        if (records[i].getFileDescription() != null && records[i].getFileDescription().startsWith("BASE")) {
                            boolean skipItem = true;
                            if (records[i].getFileName().startsWith("records/items/gq_")) {
                                skipItem = false;
                            }
                            if (records[i].getFileName().startsWith("records/tq/items/")) {
                                skipItem = false;
                            }
                            if (records[i].getFileName().equals("records/items/gearaccessories/medals/a200_medal.dbr")) {
                                skipItem = false;
                            }
                            if (skipItem) {
                                continue;
                            }
                        }
                        if (records[i].getItemName() == null) {
                            if (records[i].getItemNameTag() != null && !suppressTagWarning) {
                                final Object[] args = {records[i].getItemNameTag(), "tags_items.txt"};
                                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_TAG_NOT_FOUND", args);
                                GDMsgLogger.addWarning(msg);
                            }
                        } else if (records[i].getBitmapID() != null) {
                            DBItem.insert(records[i]);
                        }
                    }
                } catch (Exception ex) {
                    GDMsgLogger.addError(ex);
                }
            }
        }
        clearBuffers();
        if (GDMsgLogger.severeErrorsInLog()) {
            throw new SQLException(GDMsgFormatter.getString(GDMsgFormatter.rbMsg, "ERR_DB_IMPORT"));
        }
    }

    public static boolean updateStash() {
        boolean success = false;
        try {
            boolean updated = false;
            if (GDDBUtil.doesExist("jdbc:derby:db;")) {
                if (!updated && GDDBUtil.tableExists(getOldConnection(), "STASH_ITEM_V3")) {
                    moveStash(getOldConnection(), getConnection(), "STASH_ITEM_V3");
                    updated = true;
                }
                if (!updated && GDDBUtil.tableExists(getOldConnection(), "STASH_ITEM_V2")) {
                    moveStash(getOldConnection(), getConnection(), "STASH_ITEM_V2");
                    updated = true;
                }
                if (!updated && GDDBUtil.tableExists(getOldConnection(), "STASH_ITEM")) {
                    moveStash(getOldConnection(), getConnection(), "STASH_ITEM");
                    updated = true;
                }
            }
            if (!updated) {
                boolean converted = false;
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V9")) {
                    convertStash("STASH_ITEM_V9");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V8")) {
                    convertStash("STASH_ITEM_V8");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V7")) {
                    convertStash("STASH_ITEM_V7");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V6")) {
                    convertStash("STASH_ITEM_V6");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V5")) {
                    convertStash("STASH_ITEM_V5");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V4")) {
                    convertStash("STASH_ITEM_V4");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V3")) {
                    convertStash("STASH_ITEM_V3");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM_V2")) {
                    convertStash("STASH_ITEM_V2");
                    converted = true;
                }
                if (!converted && GDDBUtil.tableExists("STASH_ITEM")) {
                    convertStash("STASH_ITEM");
                    converted = true;
                }
            }
            updateDependent();
            success = !GDMsgLogger.severeErrorsInLog();
        } catch (SQLException ex) {
            GDMsgLogger.addError(ex);
        } catch (Exception ex2) {
            GDMsgLogger.addError(ex2);
        }
        return success;
    }

    private static void convertStash(final String tableName) throws SQLException {
        if (GDDBUtil.tableExists(tableName)) {
            final List<DBStashItem> items = DBStashItem.getAllOld(getConnection(), tableName);
            DBStashItem.convertStash(tableName, items);
            DBStashItem.dropOldStash(getConnection(), tableName);
        }
    }

    private static void moveStash(final Connection connOld, final Connection connNew, final String tableName) throws SQLException {
        if (GDDBUtil.tableExists(connOld, tableName)) {
            final List<DBStashItem> items = DBStashItem.getAllOld(connOld, tableName);
            DBStashItem.moveStash(connOld, connNew, tableName, items);
            DBStashItem.dropOldStash(connOld, tableName);
        }
    }

    public static void updateDB() {
        DBEngineGame.reset();
        DBEnginePlayer.reset();
        DBEngineLevel.reset();
        DBEngineSkillMaster.reset();
        DBSkill.updateDB();
        DBSkill.clearBuffer();
        DBSkillBonus.updateDB();
        DBSkillModifier.updateDB();
    }

    private static void updateDependent() throws SQLException {
        if (GDDBUtil.tableExists("STASH_ITEM_V10")) {
            final List<DBStashItem> items = DBStashItem.getAll();
            DBStashItem.updateDependentFields(items);
        }
    }
}
